{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import our libraries\n",
    "import win32com.client\n",
    "import pandas as pd\n",
    "\n",
    "# get the active instance of excel\n",
    "ExcelApp = win32com.client.GetActiveObject(\"Excel.Application\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a reference to the workbook I am working out of.\n",
    "workbook = ExcelApp.Workbooks(\"RangeBook.xlsm\")\n",
    "\n",
    "# create a reference to a range of cells\n",
    "ExcRng = ExcelApp.Range(\"A1:C5\").Value\n",
    "\n",
    "# load data into a pandas series\n",
    "raw_data = pd.Series(ExcRng)\n",
    "\n",
    "# get the column names and the raw data\n",
    "column_names = raw_data[0]\n",
    "record_data = raw_data[1:]\n",
    "\n",
    "# create a new data frame \n",
    "# SIDE NOTE - DUPLICATES SEEM TO CAUSE ISSUES WHEN CONVERTING TO A RECORDSET.\n",
    "data_frame = pd.DataFrame(data = list(record_data), \n",
    "                          columns = column_names,\n",
    "                          index = [1,2,3,4])\n",
    "\n",
    "# get the row count & column count\n",
    "row_count = data_frame.shape[0]\n",
    "col_count = data_frame.shape[1]\n",
    "\n",
    "# get the records from our data frame\n",
    "rec_array = data_frame.to_records()\n",
    "rec_array = rec_array.tolist()\n",
    "\n",
    "# reference our excel worksheet\n",
    "ExcelWrksht = workbook.Worksheets(\"One\")\n",
    "\n",
    "# get the starting cell & ending cell for the record set.\n",
    "start_cell = ExcelWrksht.Cells(2, 6)\n",
    "ending_cell = ExcelWrksht.Cells(2 + row_count - 1, 6 + col_count)\n",
    "\n",
    "# populate the excel range\n",
    "ExcelApp.Range(\"G1:I1\").Value = column_names\n",
    "ExcelApp.Range(start_cell, ending_cell).Value = rec_array"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}